package gro.ecorrdata.edata.controller;

import java.util.*;

import javax.servlet.http.HttpServletRequest;

import com.mysql.cj.xdevapi.Result;

// import org.springframework.web.bind.annotation.CrossOrigin;
// import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import gro.ecorrdata.edata.annotation.NoneAuth;
import gro.ecorrdata.edata.model.BaseResponse;
import gro.ecorrdata.edata.util.token.TokenModel;
import gro.ecorrdata.edata.util.token.TokenHelper;
import gro.ecorrdata.edata.controller.UserController;
import gro.ecorrdata.edata.model.EDataClassModel;


/**
 * EDataTable
 */
@RestController
@RequestMapping("/edata")
public class EDataController {
    private EDataClassModel eDataClassModel;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private UserController userController;

    @Autowired
    private TokenHelper tokenHelper;

    public void setEDataClassModel(Integer id) {
        String sql = "select id, pid, classname,tablename from edata_class where isdel=0 and id=?";
        try {
            Map<String, Object> result = jdbcTemplate.queryForMap(sql, id);
            this.eDataClassModel = new EDataClassModel((Integer) result.get("id"), (Integer) result.get("pid"), (String) result.get("classname"),
            (String) result.get("tablename"), (Integer) result.get("onclick"), (String) result.get("ctime"), (String) result.get("note"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @NoneAuth
    @RequestMapping("/list")
    public BaseResponse<Map<String, Object>> getEDataList(Integer classid, Integer page) {
        if (classid==0) {
            String sql1 = "select count(*) as total  from news where isdel=0";
            Map<String, Object> result = jdbcTemplate.queryForMap(sql1);
            String sql2 = "select id,title,username,newstime,onclick from news where isdel=0 order by id desc limit "+(page-1)*15+", 15";
            try {
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2);
                result.put("list", list);
                result.put("classInfo", null);
                return new BaseResponse<>(true, "查询成功", result);
            } catch (Exception e) {
                e.printStackTrace();
                return new BaseResponse<>(false, "查询失败1", null);
            }
        } else {
            setEDataClassModel(classid);
            String tablename = this.eDataClassModel.getTablename();

            // System.out.println(tablename);
            try {
                String sql1 = "select count(*) as total  from " + tablename + " where isdel=0";
                Map<String, Object> result = jdbcTemplate.queryForMap(sql1);
                String sql2 = "select id,title,username,newstime,onclick from "+tablename+" where isdel=0 and classid="+classid+" order by id desc limit "+(page-1)*15+", 15";
                try {
                    List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2);
                    result.put("list", list);
                    result.put("classInfo", this.eDataClassModel);
                    return new BaseResponse<>(true, "查询成功", result);
                } catch (Exception e) {
                    e.printStackTrace();
                    return new BaseResponse<>(false, "查询失败1", null);
                }
            }catch (Exception e) {
                e.printStackTrace();
                return new BaseResponse<>(false, "查询失败2", null);
            }
        }
    }

    @NoneAuth
    @RequestMapping("/extend_column")
    public BaseResponse<List<Map<String, Object>>> getEDataTableColumByClassid(Integer classid) {
        setEDataClassModel(classid);

        String tablename = this.eDataClassModel.getTablename();
        Integer tableid = getEDataTableIdByTablename(tablename);
        List<Map<String, Object>> result = getEDataTableColumnByTableId(tableid);

        if (result != null) {
            return new BaseResponse<>(true, "查询成功", result);
        } else {
            return new BaseResponse<>(true, "查询失败", null);
        }
    }

    public Integer getEDataTableIdByTablename(String tablename) {
        Integer id = 0;
        String sql ="SELECT `id` FROM `edata_table` where tableNameEN=?";
        try {
            Map<String, Object> resId = jdbcTemplate.queryForMap(sql, tablename);
            id = Integer.parseInt(resId.get("id").toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }
    
    public List<Map<String, Object>> getEDataTableColumnByTableId(Integer tableid) {

        String sql = "select columnNameEN,columnNameCN,isDisplay,isSearch from edata_table_column where  table_id=?";
        try {
            List<Map<String, Object>> res = jdbcTemplate.queryForList(sql, tableid);
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public String makeColumnFormat(String column) {
        String reColumn = "";
        if (column.equals("Info_Date") || column.equals("Test_Time") || column.equals("Test_Time_End")) {
            reColumn = "FROM_UNIXTIME(" + column +", '%Y-%m-%d') as " + column;
        } else {
            reColumn = column;
        }
        return reColumn;
    }

    @NoneAuth
    @RequestMapping("/getedata")
    public BaseResponse<Map<String, Object>> getEData(Integer classid, Integer id) {
        setEDataClassModel(classid);

        String tablename = this.eDataClassModel.getTablename();
        // System.out.println(tablename);
        // System.out.println(id);
        String classname = this.eDataClassModel.getClassname();

        Integer tableid = getEDataTableIdByTablename(tablename);
        
        List<Map<String, Object>> columnList = getEDataTableColumnByTableId(tableid);
        // System.out.println(columnList);
        String additionalColumn = "";
        if (columnList != null) {
            for (int i = 0; i < columnList.size(); i++) {
                Map<String,Object> map = columnList.get(i);
                String column = makeColumnFormat((String) map.get("columnNameEN"));
                additionalColumn += ", " + column;
            }
        }
        
        Map<String, Object> result = new HashMap<String, Object>();
        String sql = "select id, title, onclick, newstime"+additionalColumn+" from "+tablename+" where isdel=0 and id=?";
        // System.out.println(sql+id);
        // 更新点击次数+1
        String sqlOnclick = "UPDATE "+ tablename +" SET `onclick`=`onclick`+1 where id=?";

        try {
            Map<String, Object> tableData = jdbcTemplate.queryForMap(sql, id);

            int ionclick = jdbcTemplate.update(sqlOnclick, id);

            result.put("tableData", tableData);
            result.put("classname", classname);
            result.put("columnList", columnList);
            return new BaseResponse<>(true, "查询成功", result);
        } catch (Exception e) {
            return new BaseResponse<>(false, "查询失败", null);
        }
    }

    @PostMapping("/updateedata")
    public BaseResponse<Object> updateEData(HttpServletRequest request) {
        boolean result = false;
        String id = request.getParameter("id");
        String title = request.getParameter("title");
        if (title==null || title== "") {
            return new BaseResponse<>(false, "标题不能为空", null);
        }
        Integer classid = Integer.parseInt(request.getParameter("classid"));
        if (classid==null || classid==0) {
            return new BaseResponse<>(false, "栏目不可为空", null);
        }
        setEDataClassModel(classid);
        // 数据表名
        String tablename = this.eDataClassModel.getTablename();
        Integer tableid = getEDataTableIdByTablename(tablename);
        // 字段名
        List<Map<String, Object>> tableColumn = getEDataTableColumnByTableId(tableid);
        
        if (id == null || id=="") {  // 添加

            String authStr = request.getHeader("authStr");
            TokenModel model = tokenHelper.get(authStr);
            Integer userId = model.getUserId();
            // System.out.println(userId);
            Map<String, Object> userInfo = userController.getUserInfoById(userId);

            String values = "'"+ classid +"'" ;
            if (request.getParameter("onclick")==null || request.getParameter("onclick")=="") {
                values += ",'0'" ;
            } else {
                values += ",'"+ request.getParameter("onclick") +"'" ;
            }
            values += ",'"+ userId +"'" ;
            values += ",'"+ userInfo.get("username") +"'" ;
            values +=  ",'"+ title +"'" ;
            Long exptime = (Long) (System.currentTimeMillis()/1000);
            values += ",'"+ exptime +"'" ;
            
            String columns = "";
            for (Map<String,Object> map : tableColumn) {
                String column = (String) map.get("columnNameEN");
                columns += ", "+ column;
                if (column.equals("Info_Date") || column.equals("Test_Time") || column.equals("Test_Time_End")) {
                    if (request.getParameter(column)==null || request.getParameter(column)=="") {
                        values +=  ",'0'" ;
                    }else{
                        values +=  ",UNIX_TIMESTAMP('"+ request.getParameter(column) +"')" ;
                    }
                } else {
                    // System.out.println(column);
                    if (request.getParameter(column)==null || request.getParameter(column)=="") {
                        values += ",''" ;
                    } else {
                        values += ",'"+ request.getParameter(column) +"'" ;
                    }
                }
            }

            String sql = "INSERT INTO "+tablename+"(classid, onclick, userid, username, title, newstime";
            sql +=  columns + ") VALUES(" + values + ")";
            // System.out.println(sql);
            //  更新class infos
            String sqlInfos = "UPDATE edata_class SET `infos`=`infos`+1 where id=?";
            try {
                int itemp = jdbcTemplate.update(sql);
                jdbcTemplate.update(sqlInfos, classid);
                result =true;   
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {   // 修改
          
            String setString = "`title`='"+title+"'";
            if (request.getParameter("onclick")==null || request.getParameter("onclick")=="") {
                setString += ", `onclick`='0'";
            } else {
                setString += ", `onclick`='"+request.getParameter("onclick")+"'";
            }

            for (Map<String, Object> map:tableColumn) {
                String column = (String) map.get("columnNameEN");
                if (column.equals("Info_Date") || column.equals("Test_Time") || column.equals("Test_Time_End")) {
                    if (request.getParameter(column)==null || request.getParameter(column)=="") {
                        setString +=  ", `"+column+"`='0'" ;
                    }else{
                        setString +=  ", `"+column+"`=UNIX_TIMESTAMP('"+ request.getParameter(column) +"')" ;
                    }
                } else {
                    // System.out.println(column);
                    if (request.getParameter(column)==null || request.getParameter(column)=="") {
                        setString += ", `"+column+"`=''" ;
                    } else {
                        setString += ", `"+column+"`='"+ request.getParameter(column) +"'" ;
                    }
                }
            }            
            String sql = "UPDATE "+ tablename + " SET "+setString + "where `id`=?";
            // System.out.println(sql);

            int itemp = jdbcTemplate.update(sql, id);

            if (itemp > 0) {
                result =true;
            }   
        }

        if (result==true) {
            System.out.println("Ok!");
            return new BaseResponse<>(true, "操作成功!", null);
        } else {
            System.out.println("Error!");
            return new BaseResponse<>(false, "操作失败!", null);
        }
    }

    @PostMapping("/delete")
    public BaseResponse<Object> deleteEdata(Integer id, Integer classid) {
        boolean result = false;
        if (id == null || classid ==null) {
            return new BaseResponse<>(false, "找不到要删除的数据", null);
        }
        setEDataClassModel(classid);
        String tablename = this.eDataClassModel.getTablename();

        String sql = "UPDATE " + tablename + " SET `isdel`=1 where `id`=?";
        // System.out.println(sql);
        // System.out.println(id);
        try {
            jdbcTemplate.update(sql, id);
            
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
            result = false;
        }
        return new BaseResponse<>(result, "操作成功", null);
    }
    
    @NoneAuth
    @RequestMapping("/getlist")
    public BaseResponse<Map<String, Object>> getEDataList(Integer classid, Integer page, String q) {
        setEDataClassModel(classid);
        String tablename = this.eDataClassModel.getTablename();

        // System.out.println(tablename);
        try {
            String sql1 = "select count(*) as total  from " + tablename + " where isdel=0  and classid="+classid+" and title LIKE '%"+q+"%' ";
            Map<String, Object> result = jdbcTemplate.queryForMap(sql1);
            String sql2 = "select id,title,username,newstime,onclick from "+tablename+" where isdel=0 and classid="+classid+" and title LIKE '%"+q+"%' order by id desc limit "+(page-1)*15+", 15";
            try {
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2);
                result.put("list", list);
                result.put("classInfo", this.eDataClassModel);
                return new BaseResponse<>(true, "查询成功", result);
            } catch (Exception e) {
                e.printStackTrace();
                return new BaseResponse<>(false, "查询失败1", null);
            }
        }catch (Exception e) {
            e.printStackTrace();
            return new BaseResponse<>(false, "查询失败2", null);
        }
    }

    @NoneAuth
    @RequestMapping("/getplist")
    public BaseResponse<Map<String, Object>> getPList(Integer classid, Integer page, Integer limits) {
        Map<String, Object> result = new HashMap<String, Object>();
        if (page==null) {
            page = 1;
        }
        if (limits==null) {
            limits = 5;
        }
        setEDataClassModel(classid);

        Map<String, Object> photoList = getPhotoList(classid, page, limits);
        result.put("photoList", photoList);
        result.put("classInfo", this.eDataClassModel);
        return new BaseResponse<>(true, "查询成功", result);

    }

    public Map<String, Object> getPhotoList(Integer classid, Integer page, Integer limits) {
        // System.out.println(limits);
        try {
            String sql1 = "select count(*) as total  from photo where isdel=0  and classid="+classid;
            Map<String, Object> result = jdbcTemplate.queryForMap(sql1);
            String sql2 = "select id,title,username,newstime,onclick,titlepic from photo where isdel=0 and classid="+classid+" order by id desc limit "+(page-1)*limits+", "+limits;
            try {
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2);
                result.put("list", list);
                return result;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @NoneAuth
    @RequestMapping("/getphoto")
    public BaseResponse<Map<String, Object>> getPhoto(Integer id) {
        boolean boolResult = false;
        Map<String, Object> result = new HashMap<String, Object>();
        String msg= "";

        String sql1 = "select * from photo where isdel=0  and id=" + id;
        try{
            Map<String, Object> photoData = jdbcTemplate.queryForMap(sql1);
            Integer classid = Integer.parseInt(photoData.get("classid").toString());
            setEDataClassModel(classid);
            Map<String, Object> photoList = getPhotoList(classid, 1, 6);
            Map<String, Object> nextPrev = getNextPrev(id, classid);

            result.put("photoData", photoData);
            result.put("classData", this.eDataClassModel);
            result.put("photoList", photoList);
            result.put("nextPrev", nextPrev);
            boolResult = true;
            msg +="查询成功";
        } catch(Exception e){
            e.printStackTrace();
            msg +="查询失败";
        }
        return new BaseResponse<>(boolResult, msg, result);
    }

    // @NoneAuth
    // @RequestMapping("/getphotonext")
    public Map<String, Object> getNextPrev(Integer id, Integer cid) {
        Map<String, Object> result = new HashMap<String, Object>();
        Map<String, Object> dataPrev = new HashMap<String, Object>();
        Map<String, Object> dataNext = new HashMap<String, Object>();
        
        String sqlIdList = "select id from photo where classid=? and isdel=0";
        
        List<Map<String, Object>> idList = jdbcTemplate.queryForList(sqlIdList, cid);
        Integer counts = idList.size();
        // System.out.println(counts);
        
        ArrayList<Integer> ids = new ArrayList<Integer>();
        for(Map<String, Object> map: idList){
            ids.add(Integer.parseInt(map.get("id").toString()));
        }
        // System.out.println(ids);
        Integer nId, pId;
        if (counts==1||counts==0) {
            dataPrev = null;
            dataNext = null;
        } else {
            Integer key = ids.indexOf(id);
            // System.out.println(key);
            if (key == 0) {
                dataPrev = null;
                nId = ids.get(1);
                dataNext = getPhotoTitle(nId);
            } else if (key == counts - 1) {
                pId = ids.get(key - 1);
                dataPrev = getPhotoTitle(pId);
                dataNext = null;
            } else {
                pId = ids.get(key - 1);
                dataPrev = getPhotoTitle(pId);
                nId = ids.get(key + 1);
                dataNext = getPhotoTitle(nId);
            }
        }
        result.put("dataPrev", dataPrev);
        result.put("dataNext", dataNext);
        return result;
    }

    public Map<String, Object> getPhotoTitle(Integer id) {
        String sql = "select id, title, titlepic from photo where isdel=0 and id=?";
        try {
            Map<String, Object> result = jdbcTemplate.queryForMap(sql, id);
            return result;
        } catch (Exception e) {
            //TODO: handle exception
            return null;
        }
    }

    @RequestMapping("/updatephoto")
    @PostMapping
    public BaseResponse<Object> updatePhoto(HttpServletRequest request) {
        boolean result = false;
        String id = request.getParameter("id");
        String title = request.getParameter("title");
        if (title==null || title== "") {
            return new BaseResponse<>(false, "标题不能为空", null);
        }
        Integer classid = Integer.parseInt(request.getParameter("classid"));
        if (classid==null || classid==0) {
            return new BaseResponse<>(false, "栏目不可为空", null);
        }
        setEDataClassModel(classid);
        // 数据表名
        // String tablename = this.eDataClassModel.getTablename();
        // Integer tableid = getEDataTableIdByTablename(tablename);
        // // 字段名
        // List<Map<String, Object>> tableColumn = getEDataTableColumnByTableId(tableid);

        String keyword = (request.getParameter("keyword")==null || request.getParameter("keyword")=="") ? "" : request.getParameter("keyword");
        String writer = (request.getParameter("writer")==null || request.getParameter("writer")=="") ? "" : request.getParameter("writer");
        String source = (request.getParameter("source")==null || request.getParameter("source")=="") ? "" : request.getParameter("source");
        String titlepic = (request.getParameter("titlepic")==null || request.getParameter("titlepic")=="") ? "" : request.getParameter("titlepic");
        String morepic = (request.getParameter("morepic")==null || request.getParameter("morepic")=="") ? "" : request.getParameter("morepic");
        String onclick = (request.getParameter("onclick")==null || request.getParameter("onclick")=="") ? "0" : request.getParameter("onclick");
        String note = (request.getParameter("note")==null || request.getParameter("note")=="") ? "" : request.getParameter("note");
        Long nowtime = (Long) (System.currentTimeMillis()/1000);
        if (id == null || id=="") {  // 添加

            String authStr = request.getHeader("authStr");
            TokenModel model = tokenHelper.get(authStr);
            Integer userId = model.getUserId();
            // System.out.println(userId);
            Map<String, Object> userInfo = userController.getUserInfoById(userId);
            
            String sql = "INSERT INTO photo(classid,onclick,userid,username,title,newstime,keyword,writer,source, titlepic,morepic,note) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

            Object[] obj = new Object[]{classid,onclick,userId,userInfo.get("username"),title,nowtime,keyword,writer,source, titlepic,morepic,note};
            System.out.println(sql);
            //  更新class infos
            String sqlInfos = "UPDATE edata_class SET `infos`=`infos`+1 where id=?";
            try {
                int itemp = jdbcTemplate.update(sql, obj);
                jdbcTemplate.update(sqlInfos, classid);
                result =true;   
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {   // 修改
            String sql = "UPDATE photo SET  `title`=?,`keyword`=?,`writer`=?,`source`=?,`onclick`=?,`titlepic`=?,`morepic`=?,`note`=?,`modify_time`=? where `id`=?";
            Object[] obj = new Object[]{title,keyword,writer,source,onclick,titlepic,morepic,note,nowtime,id};
            try {
                int itemp = jdbcTemplate.update(sql, obj);
                result =true;   
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (result==true) {
            System.out.println("Ok!");
            return new BaseResponse<>(true, "操作成功!", null);
        } else {
            System.out.println("Error!");
            return new BaseResponse<>(false, "操作失败!", null);
        }
    }

    @NoneAuth
    @RequestMapping("/getonephoto")
    public BaseResponse<Map<String, Object>> getOnePhoto(Integer id) {
        boolean boolResult = false;
        Map<String, Object> result = new HashMap<String, Object>();
        String msg= "";

        String sql1 = "select * from photo where isdel=0  and id=" + id;
        try{
            Map<String, Object> photoData = jdbcTemplate.queryForMap(sql1);

            result.put("photoData", photoData);
            boolResult = true;
            msg +="查询成功";
        } catch(Exception e){
            e.printStackTrace();
            msg +="查询失败";
        }
        return new BaseResponse<>(boolResult, msg, result);
    }
} 